package com.gai.shop.dao.impl;

import com.gai.shop.dao.ProductDao;
import com.gai.shop.entity.Page;
import com.gai.shop.entity.Product;
import com.gai.shop.entity.Type;
import com.gai.shop.util.DataBaseUtil;
import com.gai.shop.util.StrUtils;
import org.apache.commons.beanutils.BeanUtils;
import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.handlers.*;

import java.util.ArrayList;
import java.util.List;
import java.util.Map;

public class ProductDaoImpl implements ProductDao {
    private final QueryRunner QR = new QueryRunner();

    /**
     * 使用类别id获取商品数据的总行数
     * @param tid
     * @return
     * @throws Exception
     */
    @Override
    public Long selectTotalData(Integer tid) throws Exception {
        return QR.query(DataBaseUtil.getConnection(),
                "SELECT COUNT(pid) FROM product WHERE tid = ?;",
                new ScalarHandler<>(),
                tid);
    }

    /**
     * 按照商品分类进行分页数据查询
     * @param tid
     * @param page
     * @return
     * @throws Exception
     */
    @Override
    public List<Product> selectProductLimitByTid(Integer tid, Page page) throws Exception {
        return QR.query(DataBaseUtil.getConnection(),
                "SELECT * FROM product WHERE tid = ? LIMIT ?,?;",
                new BeanListHandler<>(Product.class),
                tid,
                page.getShowNumberFirstIndex(),
                page.getShowNumber());
    }

    /**
     * 使用商品主键查找商品
     * @param pid
     * @return
     * @throws Exception
     */
//    @Override
//    public Product selectProductByPid(Integer pid) throws Exception {
//        return QR.query(DataBaseUtil.getConnection(),
//                "SELECT * FROM product WHERE pid = ?;",
//                new BeanHandler<>(Product.class),
//                pid);
//    }


    @Override
    public Product selectProductByPid(Integer pid) throws Exception {
        String sql = "SELECT p.`pid`,p.`pname`,p.`pinfo`,p.`pprice`,p.`pstate`,p.ptime,p.pimage,t.`tname`,t.`tinfo`,t.`tid`" +
                "FROM product p,`type` t WHERE p.`tid` = t.`tid` AND pid = ?;";
        Map<String, Object> query = QR.query(DataBaseUtil.getConnection(),
                sql,
                new MapHandler(),
                pid);
        //声明需要封装数据的实体类
        Type t = new Type();
        Product p = new Product();
        //封装数据
        BeanUtils.populate(p,query);
        BeanUtils.populate(t,query);
        //商品封装商品类型数据
        p.setType(t);
        return p;
    }

    @Override
    public List<Product> getProListByKey(String pname, String ptime) throws Exception {
        //固定的sql语句
        String sql = "SELECT p.pid,p.tid,p.pname,p.ptime,p.pprice,p.pstate,p.pinfo,t.tname,t.tinfo " +
                "FROM product p,`type` t WHERE t.tid = p.tid";
        //保存参数数据用的集合list
        List<Object> param = new ArrayList<>();
        if (StrUtils.empty(pname)) {
            sql += " AND pname LIKE ?";
            param.add("%" + pname + "%");
        }
        if (StrUtils.empty(ptime)) {
            sql += " AND ptime = ?";
            param.add(ptime);
        }
        List<Map<String, Object>> mapList = QR.query(DataBaseUtil.getConnection(),
                sql,
                new MapListHandler(),
                param.toArray());
        if (mapList == null) {
            return null;
        }
        List<Product> productList = new ArrayList<>();
        for (Map<String, Object> query : mapList) {
            //声明需要封装的实体类
            Product product = new Product();
            Type type = new Type();
            BeanUtils.populate(product,query);
            BeanUtils.populate(type,query);
            product.setType(type);
            productList.add(product);
        }
        return productList;
    }

    /**
     * 新增商品数据
     * @param product
     * @return
     * @throws Exception
     */
    @Override
    public Integer addPro(Product product) throws Exception {
        String sql = "INSERT INTO product VALUES (DEFAULT,?,?,?,?,?,?,?);";
        return QR.update(DataBaseUtil.getConnection(),
                sql,
                product.getTid(),
                product.getPname(),
                product.getPtime(),
                product.getPimage(),
                product.getPprice(),
                product.getPstate(),
                product.getPinfo());
    }
}
